﻿using AchieveBLL;
using AchieveCommon;
using AchieveEntity;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.Sql;
using System.Data.SqlClient;

namespace AchieveBLL
{
   public class CostBLL
    {
       /// <summary>
       /// 实际成本计算，规则详见设计文档
       /// </summary>
        /// <param name="ficmono">ICMO表主生产任务单号，项目单号需关联到生产任务单,使用ProjectNo</param>
       /// <returns></returns>
       public static decimal realCostCal(string ficmono)
       {
           decimal ReaCost = 0;
           string sql = @"select (
                            SELECT  sum(FAmount) as FAmount
                              FROM ICStockBillEntry 
                              where FSCStockid in( 381,384, 9948)
                              and (FEntrySelfB0445 like @ficmono or FICMOBillNO like @ficmono))+
                            (SELECT  sum(ffinishtime)*25 as ffinishtime 
                              FROM Shworkbillentry  
                              where (fentryselfz0373  like @ficmono))
                            ";
           SqlParameter[] paras = { new SqlParameter("@ficmono",SqlDbType.NVarChar,100), };
           paras[0].Value =  "%" + ficmono + "%";
           try
           {
               object c1 = SqlHelper.ExecuteScalar(SqlHelper.connStrK3, CommandType.Text, sql, paras);
               if (!c1.Equals(DBNull.Value))
               {
                   ReaCost += Convert.ToDecimal(c1);
               }        
               return ReaCost;
           }
           catch (Exception)
           {               
               throw;
           }
       }

       public static int updateProjectRealCost(string accountName,decimal ReaCost,string ProjectID,string ficmono) { 
       
        //更新项目主表 及 保存标准成本
                    string sql = @" update tbProject set IcmoNo=@IcmoNo where ProjectID = @ProjectID ;
                            if not exists (select ProjectID from tbProjectCost where ProjectID = @ProjectID )
                                                      insert into tbProjectCost(ProjectID, ReaCost,UpdateBy,UpdateTime) values(@ProjectID, @ReaCost,@UpdateBy,@UpdateTime)
                                                   else
                                                      update tbProjectCost set ReaCost=@ReaCost,UpdateBy=@UpdateBy,UpdateTime=@UpdateTime  where ProjectID =@ProjectID
                                                ";
                    SqlParameter[] paras = {                                  
                                   new SqlParameter("@UpdateTime",SqlDbType.DateTime),
                                   new SqlParameter("@UpdateBy",SqlDbType.NVarChar,100),
                                    new SqlParameter("@ReaCost",SqlDbType.Decimal),
                                     new SqlParameter("@ProjectID",SqlDbType.VarChar,50),
                                      new SqlParameter("@IcmoNo",SqlDbType.NVarChar,100),
                                   };

                    paras[0].Value = DateTime.Now;
                    paras[1].Value = accountName;
                    paras[2].Value = ReaCost;
                    paras[3].Value = ProjectID;
                    paras[4].Value = ficmono;
                    return SqlHelper.ExecuteNonQuery(SqlHelper.connStr, CommandType.Text, sql, paras);
       }

       
       /// <summary>
       /// 部门全年每月已完成工时统计，(TotalWorkTimeFrist=true如实际工时大于计划工时，则按计划工时统计)dept用名称
       /// </summary>
       /// <param name="dept"></param>
       /// <param name="year"></param>
       /// <returns></returns>
       public static Double[] DepMonthtWorkTime(string dept, Int32 year, bool TotalWorkTimeFrist=true)
       {

           double[] mv = new double[12];
           //获取范围订单
           string sql = @"select FInterID,FStartWorkDate,FEndWorkDate, 
case when FFinishTime>FTotalWorkTime then FTotalWorkTime else FFinishTime end as workTime   
from shworkbillentry where FStartWorkDate<'{1}-1-1' and FEndWorkDate>'{0}-1-1' and FFinishTime>0 and FInterID in(
select FInterID from shworkbill where FICMONO in 
(select FBillNo from ICMO where  FHeadSelfJ0181='{2}' and FStartDate<'{1}-1-1' and FFinishDate>'{0}-1-1')
)";
           if (!TotalWorkTimeFrist)
           {
            sql = @"select FInterID,FStartWorkDate,FEndWorkDate, FFinishTime as workTime   
from shworkbillentry where FStartWorkDate<'{1}-1-1' and FEndWorkDate>'{0}-1-1' and FFinishTime>0 and FInterID in(
select FInterID from shworkbill where FICMONO in 
(select FBillNo from ICMO where  FHeadSelfJ0181='{2}' and FStartDate<'{1}-1-1' and FFinishDate>'{0}-1-1')
)";
           }
           sql = string.Format(sql, year, year + 1, dept);
           DataTable dtgs = SqlHelper.GetDataTableK3(sql);
           DateTime d1, d2;
           for (int i = 0; i < dtgs.Rows.Count; i++)
           {
               d1 = (DateTime)dtgs.Rows[i]["FStartWorkDate"];
               d2 = (DateTime)dtgs.Rows[i]["FEndWorkDate"];

               if (d2 > DateTime.Now)
               {
                   d2 = DateTime.Now;
               }
               int months = (d2.Year - d1.Year) * 12 + d2.Month - d1.Month;//月跨度
               double wtpm = Convert.ToDouble( (decimal)dtgs.Rows[i]["workTime"] / (months + 1));//月均产值
               if (d1.Year < year)
               {
                   for (int j = 0; j < d2.Month; j++)
                   {
                       mv[j] += wtpm;
                   }
               }
               else
               {
                   for (int j = d1.Month - 1; j < d2.Month; j++)
                   {
                       mv[j] += wtpm;
                   }
               }
           }
           return mv;       
       }
       /// <summary>
       /// 部门全年每月领料金额统计，从源数据计算
       /// </summary>
       /// <param name="dept"></param>
       /// <param name="year"></param>
       /// <returns></returns>
       public static Double[] DepMonthtItemAmountCal(string dept, Int32 year)
       {
           double[] mv = new double[12];
           string sql0 = @"SELECT  sum(FAmount) amount, CONVERT(VarChar(7), FCheckDate, 120) as qijian 
FROM ICStockBillEntry a left join ICStockBill b on a.FInterID=b.FInterID
where b.FCheckDate between '{0}-01-01' and '{0}-12-31' 
and a.FSCStockid in( 381,384, 9948) and (
a.FICMOBillNO in(
select FBillNo from ICMO where (FHeadSelfJ0181='{2}' and FStartDate<'{1}-01-01' and FFinishDate>'{0}-01-01' ))
or a.FEntrySelfB0445 in(
select FBillNo from ICMO where (FHeadSelfJ0181='{2}' and FStartDate<'{1}-01-01' and FFinishDate>'{0}-01-01' ))
)
group by CONVERT(VarChar(7), FCheckDate, 120)  
order by CONVERT(VarChar(7), FCheckDate, 120) asc";
           string sql = string.Format(sql0,year,year+1,dept);
           DataTable dt = SqlHelper.GetDataTableK3(sql);
           for (int i = 0; i < dt.Rows.Count; i++)
           {
               string qijianindex = dt.Rows[i]["qijian"].ToString().Substring(5);
               int qj = Convert.ToInt32(qijianindex)-1;
               mv[qj] =Convert.ToDouble(dt.Rows[i]["amount"].ToString());
           } 
           return mv;
       }
       /// <summary>
       /// 从缓存中加载部门全年每月领料金额统计
       /// </summary>
       /// <param name="dept"></param>
       /// <param name="year"></param>
       /// <returns></returns>
       public static Double[] DepMonthtItemAmount(string dept, Int32 year)
       { 
           double[] mv = new double[12]; 
           string id = "1-2020";
           if (dept == "nyhb" || dept == "能源环保事业部")
           {
               id = "1-" + year.ToString();
           }
           string sql0 = @"SELECT  * from  tbCache where id='{0}'";
           string sql = string.Format(sql0, id);
           DataTable dt = SqlHelper.GetDataTableIE(sql);
           if (dt.Rows.Count==1)
           {
               for (int i = 0; i < 12; i++)
               {
                   if (dt.Rows[0]["v"+(i+1)]!=DBNull.Value)
                   {
                       mv[i] = Convert.ToDouble(dt.Rows[0]["v"+(i+1)].ToString());
                   }
                   else
                   {
                       mv[i] = 0;
                   } 
               } 
           }
          
           return mv;
       }

    }

}
